package com.gl.common.autoparts.utils;

import com.gl.basis.common.exception.BusinessException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel
 * @author LiaoQisheng
 * @date 2019/11/16 09:42
 */
public class ExcelUtils<T> {

    private static ExcelUtils instance = null;

    private void ExcelUtils () {

    }

    public static synchronized ExcelUtils getInstance() {
        if(null == instance) {
            instance = new ExcelUtils();
        }
        return instance;
    }

    /**
     * Excel 导入
     * @param file
     * @param offset
     * @param clz
     * @return
     */
    public List<? extends T> importExcel(MultipartFile file, int offset, Class<T> clz) {
       List<T> objectList = new ArrayList<>();
       List<String[]> list = this.importExcel(file, offset);
       for(String[] rowContent : list ) {
           T t = null;
           try {
               t = clz.newInstance();
               this.setAttributeValue(t, rowContent);
           } catch (NoSuchMethodException e) {
               e.printStackTrace();
           } catch (InvocationTargetException e) {
               e.printStackTrace();
           } catch (IllegalAccessException e) {
               e.printStackTrace();
           } catch (ParseException e) {
               e.printStackTrace();
           } catch (InstantiationException e) {
               e.printStackTrace();
           }
           objectList.add(t);
       }
       return objectList;
    }

    /**
     * Excel 导入
     * @param file
     * @param offset
     * @return
     * @throws BusinessException
     */
    public List<String[]> importExcel(MultipartFile file, int offset) throws BusinessException {

        boolean notNull = false;
        List<String[]> list = null;
        String fileName = file.getOriginalFilename();

        if(!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new BusinessException("上传文件格式错误");
        }

        boolean isExcel2003 = true;
        if(fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook;
            if(isExcel2003) {
                workbook = new HSSFWorkbook(inputStream);
            }else {
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            if(null != sheet) {
                notNull = true;
            }
            if(notNull) {
                int totalRows = sheet.getPhysicalNumberOfRows();
                list = new ArrayList<>();
                Row row = null;
                int totalCells = 0;
                for(int i = offset; i < totalRows; i++) {
                    row = sheet.getRow(i);
                    totalCells =row==null?0:row.getLastCellNum();
                    for(int j = 0; j < totalCells; j++) {
                        if(null != row.getCell(j)) {
                            row.getCell(j).setCellType(CellType.STRING);
                        }
                    }

                    String[] str = new String[totalCells];
                    for(int k = 0; k < totalCells; k++) {
                        if(row.getCell(k)==null) {
                            row.createCell(k).setCellValue("");
                        }
                        String cellValue = row.getCell(k).getStringCellValue();
                        if(StringUtils.isBlank(cellValue)) {
                            cellValue = "";
                        }
                        str[k] = cellValue;
                    }
                    list.add(str);
                }
                inputStream.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;

    }

    private void setAttributeValue(Object object, Object[] objects) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, ParseException {
        Field[] field = object.getClass().getDeclaredFields();
        for(int i=0; i<field.length; i++) {
            String name, type;
            Object value;
            Method m;
            name = field[i].getName().substring(0, 1).toUpperCase() + field[i].getName().substring(1);
            type = field[i].getGenericType().toString();
            switch (type) {
                case "class java.lang.String":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, objects[i].toString());
                    }
                    break;
                case "class java.lang.Integer":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Integer.parseInt(objects[i].toString()));
                    }
                    break;
                case "class java.lang.Long":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Long.parseLong(objects[i].toString()));
                    }
                    break;
                case "class java.lang.Double":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Double.parseDouble(objects[i].toString()));
                    }
                    break;
                case "class java.lang.Float":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Float.parseFloat(objects[i].toString()));
                    }
                    break;
                case "class java.lang.Boolean":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Boolean.parseBoolean(objects[i].toString()));
                    }
                    break;
                case "class java.util.Date":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object,new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(objects[i].toString()));
                    }
                    break;
                case "class java.lang.Byte":
                    m = object.getClass().getMethod("get"+name);
                    value = m.invoke(object);
                    if(null == value) {
                        m = object.getClass().getMethod("set"+name, String.class);
                        m.invoke(object, Byte.parseByte(objects[i].toString()));
                    }
                    break;
            }

        }



    }
}
